package untiring07.j2ee.basic.projectv9.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import untiring07.j2ee.basic.projectv5.model.BoardVO;
import untiring07.j2ee.basic.util.jdbc.MariaDB;

public class BoardDAO {

	private static String newSQL = "insert into boardv4(title, uid, contents) values(?,?,?)";
	private static String viewSQL = "select * from boardv4 where bdno = ?";
	private static String updateSQL = "update boardv4 set title=?, uid=?, contents=? where bdno = ?";
	private static String deleteSQL = "delete from boardv4 where bdno = ?";
	private static String findBaseSQL ="select bdno, title, uid, wdate, reades from boardv4 ";//"where ? like ? "
	private static String findOrderSQL ="order by bdno desc limit ? ,?";
	protected static String totalSQL ="select count(bdno) from boardv4";
	protected static int perPage = 20; //페이지당 게시물 수
	
	public static boolean newBoard(BoardVO b) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try {			
			conn = MariaDB.openCPConn();
			pstmt = conn.prepareStatement(newSQL);
			pstmt.setString(1, b.getTitle());
			pstmt.setString(2, b.getUid());
			pstmt.setString(3, b.getContents());
			if (pstmt.executeUpdate() > 0)
				result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, null);
		}
		return result;
	}

	public static BoardVO viewBoard(BoardVO b) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BoardVO result = new BoardVO();
		try {
			conn = MariaDB.openCPConn();
			pstmt = conn.prepareStatement(viewSQL);
			pstmt.setString(1, b.getBdno());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result.setBdno(rs.getString(1));
				result.setTitle(rs.getString(2));
				result.setUid(rs.getString(3));
				result.setWdate(rs.getString(4));
				result.setReades(rs.getString(5));
				result.setContents(rs.getString(6));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, rs);
		}
		return result;
	}

	public static boolean updateBoard(BoardVO b) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try {
			conn = MariaDB.openCPConn();
			pstmt = conn.prepareStatement(updateSQL);
			pstmt.setString(1, b.getTitle());
			pstmt.setString(2, b.getUid());
			pstmt.setString(3, b.getContents());
			pstmt.setString(4, b.getBdno());
			if (pstmt.executeUpdate() > 0)
				result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, null);
		}
		return result;
	}

	public static boolean deleteBoard(BoardVO b) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;

		try {
			conn = MariaDB.openCPConn();
			pstmt = conn.prepareStatement(deleteSQL);
			pstmt.setString(1, b.getBdno());
			if (pstmt.executeUpdate() > 0)
				result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, null);
		}

		return result;

	}
	
	public static List<BoardVO> listBoard(int startno, String ftype, String fkey){
		Connection conn =null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<BoardVO> result=new ArrayList<>();
		
		String whereSQL = " where "+ftype+" like '%"+fkey+"%' ";
		String SQL = findBaseSQL+findOrderSQL;
		
		if(!ftype.equals("") && !fkey.equals("")){
		SQL = findBaseSQL+ whereSQL+findOrderSQL;
		}
		//동적으로 생성된SQL 문 확인
		// 검색어를 입력하지 않으면 : 기본SQL문 사용
		// 검색어를 입력하면           : 검색전용 SQL 문 사용
		try{
			conn = MariaDB.openConn();
			pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, startno);
			pstmt.setInt(2, perPage);
			rs = pstmt.executeQuery();
			while ( rs.next() ) {
				BoardVO b=new BoardVO();
					b.setBdno( rs.getString(1) );
					b.setTitle( rs.getString(2) );
					b.setUid( rs.getString(3) );
					b.setWdate( rs.getString(4) );
					b.setReades( rs.getString(5) );
				result.add(b);
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			MariaDB.closeConn(conn, pstmt, rs);
		}
		
		return result;
	}
	
	public static int totalBoard(String ftype, String fkey){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int result = 0;
		String SQL = totalSQL;
		if(!ftype.equals("") && !fkey.equals("")){
			SQL += " where "+ftype+" like '%"+fkey+"%'";
		}
		
		try{
			conn=MariaDB.openConn();
			pstmt=conn.prepareStatement(SQL);
			rs=pstmt.executeQuery();
			if(rs.next()){
				result = rs.getInt(1);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			MariaDB.closeConn(conn, pstmt, rs);
		}return result;
	}

}
